const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔍 识别需要更新的实例表');
console.log('==============================');

try {
  // 搜索所有实例表
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📊 分析 ${instanceTables.length} 个实例表...\n`);
  
  // 分类实例表
  const completeeTables = [];  // 完整的表格 (如cooling_water_spray_system)
  const incompleteTables = []; // 不完整的表格 (只有3个字段)
  
  for (const table of instanceTables) {
    try {
      const tableInfo = db.prepare(`PRAGMA table_info(${table.name})`).all();
      const systemFields = ['id', 'created_at', 'updated_at'];
      const businessFields = ['device_type_code', 'instance_number', 'object_number', 'object_description'];
      
      const dataFields = tableInfo.filter(col => !systemFields.includes(col.name));
      const actualBusinessFields = dataFields.filter(col => businessFields.includes(col.name));
      const attributeFields = dataFields.filter(col => !businessFields.includes(col.name));
      
      // 按前缀分类属性字段
      const fieldsByPrefix = {
        'naming_': [],
        'position_': [],
        'base_params_': [],
        'other': []
      };
      
      attributeFields.forEach(field => {
        if (field.name.startsWith('naming_')) {
          fieldsByPrefix['naming_'].push(field.name.replace('naming_', ''));
        } else if (field.name.startsWith('position_')) {
          fieldsByPrefix['position_'].push(field.name.replace('position_', ''));
        } else if (field.name.startsWith('base_params_')) {
          fieldsByPrefix['base_params_'].push(field.name.replace('base_params_', ''));
        } else {
          fieldsByPrefix['other'].push(field.name);
        }
      });
      
      const analysis = {
        tableName: table.name,
        totalFields: tableInfo.length,
        businessFields: actualBusinessFields.length,
        attributeFields: attributeFields.length,
        prefixFields: fieldsByPrefix,
        isComplete: false
      };
      
      // 判断表格是否完整
      // 完整标准：有完整的前缀命名属性字段
      const hasNaming = fieldsByPrefix['naming_'].length > 1;
      const hasPosition = fieldsByPrefix['position_'].length > 1;
      const hasBaseParams = fieldsByPrefix['base_params_'].length > 1;
      
      analysis.isComplete = hasNaming && hasPosition && hasBaseParams;
      
      if (analysis.isComplete) {
        completeeTables.push(analysis);
      } else {
        incompleteTables.push(analysis);
      }
      
    } catch (error) {
      console.log(`❌ 分析 ${table.name} 失败: ${error.message}`);
    }
  }
  
  // 显示结果
  console.log('📊 分析结果');
  console.log('===============================');
  console.log(`✅ 完整的实例表: ${completeeTables.length} 个`);
  console.log(`⚠️  不完整的实例表: ${incompleteTables.length} 个\n`);
  
  // 显示完整的表格（参考模式）
  console.log('✅ 完整的实例表 (参考模式):');
  console.log('--------------------------------');
  completeeTables.forEach(table => {
    console.log(`📋 ${table.tableName}`);
    console.log(`  总字段: ${table.totalFields} | 属性字段: ${table.attributeFields}`);
    console.log(`  naming_: ${table.prefixFields['naming_'].length} | position_: ${table.prefixFields['position_'].length} | base_params_: ${table.prefixFields['base_params_'].length}`);
  });
  
  // 显示不完整的表格（需要更新）
  console.log('\n⚠️  不完整的实例表 (需要更新):');
  console.log('------------------------------------');
  incompleteTables.forEach(table => {
    console.log(`📋 ${table.tableName}`);
    console.log(`  总字段: ${table.totalFields} | 属性字段: ${table.attributeFields}`);
    console.log(`  naming_: ${table.prefixFields['naming_'].length} | position_: ${table.prefixFields['position_'].length} | base_params_: ${table.prefixFields['base_params_'].length}`);
    
    // 分析问题
    const issues = [];
    if (table.prefixFields['naming_'].length <= 1) issues.push('缺少完整命名字段');
    if (table.prefixFields['position_'].length <= 1) issues.push('缺少完整位置字段');
    if (table.prefixFields['base_params_'].length <= 1) issues.push('缺少完整基础参数字段');
    if (table.prefixFields['other'].length > 0) issues.push('存在非前缀字段');
    
    console.log(`  问题: ${issues.join(', ')}`);
  });
  
  // 生成更新方案
  console.log('\n🔧 更新方案分析');
  console.log('===============================');
  
  if (incompleteTables.length > 0) {
    console.log(`需要更新 ${incompleteTables.length} 个实例表，使其符合完整的前缀命名规则。\n`);
    
    console.log('📋 更新内容包括:');
    console.log('1. 📝 添加完整的命名表字段 (naming_前缀)');
    console.log('2. 📍 添加完整的位置表字段 (position_前缀)');
    console.log('3. ⚙️  添加完整的基础参数表字段 (base_params_前缀)');
    console.log('4. 🗑️  处理现有的单个或错误的属性字段');
    
    console.log('\n📐 标准实例表结构:');
    console.log('系统字段 (3个): id, created_at, updated_at');
    console.log('业务字段 (4个): device_type_code, instance_number, object_number, object_description'); 
    console.log('命名字段 (12个): naming_设计物资编码, naming_采购物资编码, etc.');
    console.log('位置字段 (10-12个): position_工厂地址, position_设计设备位号, etc.');
    console.log('基础参数字段 (变量): base_params_规格, base_params_型号, etc.');
    
    console.log('\n⚠️  注意事项:');
    console.log('- 更新会修改表结构，建议先备份');
    console.log('- 需要根据设备类型映射确定正确的属性表');
    console.log('- 现有的单字段属性数据需要迁移或重新录入');
    
    console.log('\n❓ 确认问题:');
    console.log(`是否继续更新这 ${incompleteTables.length} 个不完整的实例表？`);
    
  } else {
    console.log('🎉 所有实例表都已符合完整的前缀命名规则！');
  }
  
} catch (error) {
  console.error('❌ 分析失败:', error.message);
} finally {
  db.close();
}